﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace library
{
    public partial class 数据汇总和可视化 : Form
    {
        public MySqlConnection conn = new DBclass().getConn();
        public 数据汇总和可视化()
        {
            InitializeComponent();
        }



        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void 数据汇总和可视化_Load(object sender, EventArgs e)
        {
            conn.Open();
            string sql = "select count(*) from borrow_info where year(borrowtime)=year(NOW()) ";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                textBox2.Text = reader[0].ToString();
            }
            reader.Close();
            string sql1 = "select count(DISTINCT uID) from borrow_info where year(borrowtime)=year(NOW())";
            MySqlCommand cmd1 = new MySqlCommand(sql1, conn);
            MySqlDataReader reader1 = cmd1.ExecuteReader();
            if (reader1.Read())
            {
                textBox1.Text = reader1[0].ToString();
            }
            reader1.Close();
            string sql2 = "SELECT COUNT(*) FROM book_info where lose_whether='是'";
            MySqlCommand cmd2 = new MySqlCommand(sql2, conn);
            MySqlDataReader reader2 = cmd2.ExecuteReader();
            if (reader2.Read())
            {
                textBox3.Text = reader2[0].ToString();
            }
            reader2.Close();
            string sql3 = "SELECT COUNT(*) FROM borrow_info where returntime<act_returntime and year(borrowtime)=year(NOW())";
            MySqlCommand cmd3 = new MySqlCommand(sql3, conn);
            MySqlDataReader reader3 = cmd3.ExecuteReader();
            if (reader3.Read())
            {
                textBox4.Text = reader3[0].ToString();
            }
            reader3.Close();
            string sql4 = "SELECT COUNT(*) FROM book_info";
            MySqlCommand cmd4 = new MySqlCommand(sql4, conn);
            MySqlDataReader reader4 = cmd4.ExecuteReader();
            if (reader4.Read())
            {
                textBox5.Text = reader4[0].ToString();
            }
            reader4.Close();
            string sql5 = "SELECT COUNT(*) FROM booking_info where year(booking_time)=year(NOW())";
            MySqlCommand cmd5 = new MySqlCommand(sql5, conn);
            MySqlDataReader reader5 = cmd5.ExecuteReader();
            if (reader5.Read())
            {
                textBox6.Text = reader5[0].ToString();
            }
            reader5.Close();

            //查询借阅次数最多的十本书
            string sql6 = "select book1_info.bname,COUNT(*) from borrow_info,book_info,book1_info where book_info.bID=borrow_info.bID and book_info.ISBN=book1_info.ISBN and year(borrowtime)=year(NOW()) GROUP BY book_info.ISBN  ORDER BY COUNT(*) DESC limit 10";
            MySqlCommand cmd6 = new MySqlCommand(sql6, conn);
            MySqlDataReader reader6 = cmd6.ExecuteReader();
            List<string> xData = new List<string>();
            List<int> yData = new List<int>();
            while (reader6.Read())
            {
                xData.Add(reader6[0].ToString());
                yData.Add(Convert.ToInt32(reader6[1]));
            }
            reader6.Close();
            chart1.Series[0].Points.DataBindXY(xData, yData);
            chart1.ChartAreas[0].AxisX.LabelStyle.Angle =-45;
            chart1.ChartAreas[0].AxisX.Title = "图书名称";
            chart1.ChartAreas[0].AxisY.Title = "借阅次数";
            chart1.ChartAreas[0].Axes[0].MajorGrid.Enabled = false;
            chart1.ChartAreas[0].Axes[1].MajorGrid.Enabled = false;
            //查询类别借阅次数最多的书籍
            string sql7 = "select book1_info.type,COUNT(*) from borrow_info,book_info,book1_info where book_info.bID=borrow_info.bID and book_info.ISBN=book1_info.ISBN and year(borrowtime)=year(NOW()) GROUP BY book1_info.type  ORDER BY COUNT(*) DESC limit 10";
            MySqlCommand cmd7 = new MySqlCommand(sql7, conn);
            MySqlDataReader reader7 = cmd7.ExecuteReader();
            List<string> xData1 = new List<string>();
            List<int> yData1 = new List<int>();
            while (reader7.Read())
            {
                xData1.Add(reader7[0].ToString());
                yData1.Add(Convert.ToInt32(reader7[1]));
            }
            chart2.Series[0].Points.DataBindXY(xData1, yData1);
            chart2.Series["Series1"].LabelFormat ="{ P0}";
            chart2.Series[0].Label = "#PERCENT";
            chart2.Series["Series1"].LegendText = "#VALX";
            reader7.Close();
            //统计每专业的借阅次数
            string sql8 = "select user_info.major,COUNT(*) from borrow_info,user_info where user_info.uID=borrow_info.uID and year(borrowtime)=year(NOW())  GROUP BY major ORDER BY COUNT(*) DESC LIMIT 10";
            MySqlCommand cmd8 = new MySqlCommand(sql8, conn);
            MySqlDataReader reader8 = cmd8.ExecuteReader();
            List<string> xData3 = new List<string>();
            List<int> yData3 = new List<int>();
            while (reader8.Read())
            {
                xData3.Add(reader8[0].ToString());
                yData3.Add(Convert.ToInt32(reader8[1]));
            }
            reader8.Close();
            chart3.Series[0].Points.DataBindXY(xData3, yData3);
            chart3.ChartAreas[0].AxisX.LabelStyle.Angle = -45;
            chart3.ChartAreas[0].AxisX.Title = "专业班级";
            chart3.ChartAreas[0].AxisY.Title = "借阅次数";
            chart3.ChartAreas[0].Axes[0].MajorGrid.Enabled = false;
            chart3.ChartAreas[0].Axes[1].MajorGrid.Enabled = false;

            //查询预约次数最多的十本书
            string sql9 = "select book1_info.bname,COUNT(*) from booking_info,book1_info where book1_info.ISBN=booking_info.ISBN and book1_info.ISBN=book1_info.ISBN and year(booking_time)=year(NOW()) GROUP BY booking_info.ISBN  ORDER BY COUNT(*) DESC limit 10";
            MySqlCommand cmd9 = new MySqlCommand(sql9, conn);
            MySqlDataReader reader9 = cmd9.ExecuteReader();
            List<string> xData4 = new List<string>();
            List<int> yData4 = new List<int>();
            while (reader9.Read())
            {
                xData4.Add(reader9[0].ToString());
                yData4.Add(Convert.ToInt32(reader9[1]));
            }
            reader9.Close();
            chart4.Series[0].Points.DataBindXY(xData4, yData4);
            chart4.ChartAreas[0].AxisX.LabelStyle.Angle = -45;
            chart4.ChartAreas[0].AxisX.Title = "图书名称";
            chart4.ChartAreas[0].AxisY.Title = "预约次数";
            chart4.ChartAreas[0].Axes[0].MajorGrid.Enabled = false;
            chart4.ChartAreas[0].Axes[1].MajorGrid.Enabled = false;

            string sql10 = "select year(borrowtime),count(*) from borrow_info GROUP BY year(borrowtime) ORDER BY year(borrowtime) limit 10";
            MySqlCommand cmd10 = new MySqlCommand(sql10, conn);
            MySqlDataReader reader10 = cmd10.ExecuteReader();
            List<string> xData10 = new List<string>();
            List<int> yData10 = new List<int>();
            while (reader10.Read())
            {
                xData10.Add(reader10[0].ToString());
                yData10.Add(Convert.ToInt32(reader10[1]));
            }
            reader10.Close();
            chart5.Series[0].Points.DataBindXY(xData10, yData10);
            chart5.ChartAreas[0].AxisX.LabelStyle.Angle = -45;
            chart5.ChartAreas[0].AxisX.Title = "年份";
            chart5.ChartAreas[0].AxisY.Title = "借阅次数";
            chart5.ChartAreas[0].Axes[0].MajorGrid.Enabled = false;
            chart5.ChartAreas[0].Axes[1].MajorGrid.Enabled = false;
            reader10.Close();
            conn.Close();
        }

        private void chart2_Click(object sender, EventArgs e)
        {

        }
    }
}
